﻿using System;
using System.Data;
using System.IO;
using System.Windows.Forms;
using System.ComponentModel;
using System.Text;
using System.Collections.Generic;
using System.Data.SQLite;

namespace SqliteLoader
{
	partial class ImportWizard
	{
		private Mapping _mapping;
		public SQLiteConnection Connection { get; set; }

		public ImportWizard()
		{
			InitializeComponent();
		}

		public string[] Filenames { get; set; }

		public DialogResult ShowWizard()
		{
			throw new NotImplementedException();
			/*
			if (_mapping == null)
				GenerateDefaultMapping();
			using(var form = new TablesDialog())
			{
				form.Dataset = _mapping;
				return form.ShowDialog();
			}
			*/
		}

		private void LoadTables(Mapping mapping)
		{
			mapping.TableDef.Clear();
			foreach (var tableName in SqliteUtil.GetTableNames(Connection))
				mapping.TableDef.AddTableDefRow(tableName).AcceptChanges();
		}

		public int CommitInterval { get; set; }


		private int ExecuteQuery(string sql)
		{
			try
			{
				var cmd = Connection.CreateCommand();
				cmd.CommandText = sql;
				return cmd.ExecuteNonQuery();
			}
			catch (SQLiteException ex)
			{
				throw new SQLiteException("Error executing sql: " + sql, ex);
			}
		}

		private string GetCreateTableSql(Mapping.TableDefRow table)
		{
			var builder = new StringBuilder("CREATE TABLE " + table.Name + "(");

			int i = 0;
			foreach (var column in table.GetColumnDefRows())
			{
				if (i > 0)
					builder.AppendLine(",");
				AddColumnDefSql(builder, column);
				i++;
			}
			builder.AppendLine(")");
			if (i > 0)
				return builder.ToString();
			return null;
		}

		private static void AddColumnDefSql(StringBuilder builder, Mapping.ColumnDefRow column)
		{
			builder.Append(column.Name);
			if (!column.IsDataTypeNull())
				builder.Append(" " + column.DataType);
			if (!column.Nullable)
				builder.Append(" NOT NULL");
			if (column.Unique)
				builder.Append(" UNIQUE");
		}

		private void backgroundWorker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
		{
			var backgroundWorker = (BackgroundWorker)sender;
			_totalSize = GetTotalDataSize();
			_totalDataRead = 0;

			foreach  (Mapping.TableMappingRow tabMap in _mapping.TableMapping.Rows)
				if (!backgroundWorker.CancellationPending && !tabMap.IsDestinationNull())
				{
					backgroundWorker.ReportProgress(GetProgress(_totalDataRead, _totalSize), "Importing " + tabMap.SourceFilename);
					var t = Connection.BeginTransaction();
					try
					{
						Import(tabMap);
						if (!backgroundWorker.CancellationPending)
						{
							t.Commit();
							_totalDataRead += new FileInfo(tabMap.SourceFilename).Length;
							backgroundWorker.ReportProgress(GetProgress(_totalDataRead, _totalSize), "Commit");
						}
						else
							t.Rollback();
					}
					catch
					{
						t.Rollback();
						throw;
					}
				}
		}

		private int GetProgress(long index, long count)
		{
			if (count > 0)
				return Convert.ToInt32(100.0 * index / count);
			return 0;
		}

		private long GetTotalDataSize()
		{
			long total = 0;
			foreach (Mapping.TableMappingRow mapping in _mapping.TableMapping.Rows)
				if(!mapping.IsDestinationNull())
					total += new FileInfo(mapping.SourceFilename).Length;
			return total;
		}

		private void Import(Mapping.TableMappingRow mapping)
		{
			using(var stream = new FileStream(mapping.SourceFilename, FileMode.Open))
			using (var reader = new CsvReader(stream))
			{
				if (mapping.Mode == TableMode.Create)
					CreateTable(mapping);
				else 
					UpdateTable(mapping);

				var fieldIndices = new List<int>(reader.FieldCount);
				var columnNames = new List<string>(reader.FieldCount);
				var columnMappings = mapping.GetColumnMappingRows();

				for (int i = 0; i < reader.FieldCount; i++)
				{
					var colMapping = Array.Find(columnMappings, r => string.Compare(r.Source, reader.GetName(i), true) == 0 && !r.IsDestinationNull());
					if (colMapping != null)
					{
						columnNames.Add(colMapping.Destination);
						fieldIndices.Add(i);
					}
				}
				if (columnNames.Count == 0)
				{
					_progressDlg.backgroundWorker.ReportProgress(-1, "No column mapped");
					return;
				}
				var cmd = GetInsertCommand(mapping.TableDefRow.Name, columnNames.ToArray());

				_progressDlg.backgroundWorker.ReportProgress(-1, "Reading rows...");
				int rowCount = 0;
				int u = 0;
				while (reader.Read())
					if (!_progressDlg.backgroundWorker.CancellationPending)
					{
						for (int i = 0; i < fieldIndices.Count; i++)
							((IDbDataParameter)cmd.Parameters[i]).Value = i < reader.FieldCount
								? reader.GetValue(i)
								: null;

						cmd.ExecuteNonQuery();
						rowCount++;
						if (Environment.TickCount > u)
						{
							_progressDlg.backgroundWorker.ReportProgress(GetProgress(_totalDataRead + stream.Position, _totalSize), new ProgressState(null, rowCount + " rows read."));
							u = Environment.TickCount + 50;
						}
					}

				if (!_progressDlg.backgroundWorker.CancellationPending)
					_progressDlg.backgroundWorker.ReportProgress(-1, new ProgressState(rowCount + " rows read."));
			}
		}

		private void UpdateTable(Mapping.TableMappingRow mapping)
		{
			foreach (var colMap in mapping.GetColumnMappingRows())
			{
				var col = colMap.ColumnDefRow;
				if (col != null && col.RowState == DataRowState.Added)
				{
					var builder = new StringBuilder("ALTER TABLE " + mapping.Destination + " ADD COLUMN ");
					AddColumnDefSql(builder, col);
					_progressDlg.backgroundWorker.ReportProgress(-1, string.Format("Add column {0} to table {1}", col.Name, mapping.Destination));
					ExecuteQuery(builder.ToString());
				}
			}
		}

		private void CreateTable(Mapping.TableMappingRow mapping)
		{
			var sql = GetCreateTableSql(mapping.TableDefRow);
			if (sql == null) return;
		
			_progressDlg.backgroundWorker.ReportProgress(-1, "Creating table " + mapping.Destination);
			ExecuteQuery(sql);
		}

		private IDbCommand GetInsertCommand(string tableName, string[] columnNames)
		{
			var cmd = Connection.CreateCommand();
			var builder = new StringBuilder("INSERT INTO " + tableName + " (");

			builder.Append(string.Join(", ", columnNames));
			builder.AppendLine(")");
			builder.Append("VALUES (:" + string.Join(", :", columnNames) + ")");

			cmd.CommandText = builder.ToString();
			for (int i = 0; i < columnNames.Length; i++)
			{
				var param = cmd.CreateParameter();
				param.ParameterName = columnNames[i];
				cmd.Parameters.Add(param);
			}
			return cmd;
		}

		public void GenerateDefaultMapping()
		{
			_mapping = new Mapping();
			LoadTables(_mapping);

			foreach (var filename in Filenames)
			{
				var row = _mapping.TableMapping.NewTableMappingRow();
				row.Source = Path.GetFileName(filename);
				row.SourceFilename = filename;
				var tablename = SqliteUtil.GetValidTableName(Path.GetFileNameWithoutExtension(filename)) ?? "NewTable";
				var table = row.TableDefRow = _mapping.TableDef.FindByName(tablename);

				if (row.TableDefRow == null)
				{
					row.TableDefRow = _mapping.TableDef.AddTableDefRow(tablename);
					row.Mode = TableMode.Create;
				}
				else
				{
					row.Mode = TableMode.Append;
					if (table.GetColumnDefRows().Length == 0)
						LoadColumn(table);
				}
				_mapping.TableMapping.Rows.Add(row);

				GenerateColumnMapping(row);
			}
		}

		private void LoadColumn(Mapping.TableDefRow table)
		{
			foreach (var columnInfo in SqliteUtil.GetColumnInfos(Connection, table.Name))
				_mapping.ColumnDef.AddColumnDefRow(
					table,
					columnInfo.Name,
					columnInfo.DataType,
					false,
					columnInfo.NotNull).AcceptChanges();
		}

		private void GenerateColumnMapping(Mapping.TableMappingRow tabMapping)
		{
			var table = tabMapping.TableDefRow;
			using (var reader = new CsvReader(tabMapping.SourceFilename))
			{
				reader.Analyze();
				for (int i = 0; i < reader.FieldCount; i++)
				{
					var name = SqliteUtil.GetValidIdentifier(reader.GetName(i)) ?? "NewColumn" + i;
					var column = Array.Find(table.GetColumnDefRows(), r => string.Compare(r.Name, name, true) == 0)
						?? _mapping.ColumnDef.AddColumnDefRow(table, name, "Text", false, true);
					var columnMapping = _mapping.ColumnMapping.AddColumnMappingRow(reader.GetName(i), name, tabMapping, column);
				}
			}
		}

		private ProgressDialog _progressDlg;
		private long _totalSize;
		private long _totalDataRead;

		public void Start()
		{
			//if (wizard.ShowWizard() == DialogResult.OK)
			using (_progressDlg = new ProgressDialog())
			{
				_progressDlg.Text = "Import";
				_progressDlg.backgroundWorker.RunWorkerAsync();
				_progressDlg.backgroundWorker.DoWork += new DoWorkEventHandler(backgroundWorker_DoWork);
				_progressDlg.backgroundWorker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(backgroundWorker_RunWorkerCompleted);
				_progressDlg.backgroundWorker.ProgressChanged += new ProgressChangedEventHandler(backgroundWorker_ProgressChanged);
				_progressDlg.ShowDialog();
			}
			_progressDlg = null;
		}

		void backgroundWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
		{
			if (e.ProgressPercentage >= 0)
				_progressDlg.Text = string.Format("{0}% Import", e.ProgressPercentage);
		}

		void backgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
		{
			if (e.Cancelled)
				_progressDlg.AddMessage(_progressDlg.statusLabel.Text = "Cancelled by user");
			else if (e.Error != null)
			{
				_progressDlg.AddMessage("Import error: " + e.Error.Message);
				_progressDlg.statusLabel.Text = "Aborted by error";
			}
			else
				_progressDlg.AddMessage(_progressDlg.statusLabel.Text = "Import completed");
		}
	}
}
